<?php

namespace App\Common\Services;

use Illuminate\Support\Facades\Log;
use Maatwebsite\Excel\Concerns\WithStyles;
use Maatwebsite\Excel\Concerns\FromCollection;
use PhpOffice\PhpSpreadsheet\Reader\Xls\Style\Border as XlsStyleBorder;
use PhpOffice\PhpSpreadsheet\Reader\Xml\Style\Border;
use PhpOffice\PhpSpreadsheet\Style\Border as StyleBorder;
use PhpOffice\PhpSpreadsheet\Worksheet\Worksheet;

class CommonExcelExport implements FromCollection, WithStyles
{

    //公共导出
    private $row;
    private $data;
    private $datawith;
    private $mergeCell;
    private $isHeji;

    public function __construct(array $row, array $data, array $datawith = [], array $mergeCell = [], bool $isHeji = true)
    {
        $this->row = $row;
        $this->data = $data;
        $this->datawith = $datawith;
        $this->mergeCell = $mergeCell;
        $this->isHeji = $isHeji;
    }

    /**
     * @return \Illuminate\Support\Collection
     */
    public function collection()
    {
        $row = $this->row;
        $data = $this->data;
        $isHeji = $this->isHeji;

        //设置表头
        foreach ($row[count($row) - 1] as $key => $value) {
            $key_arr[] = $key;
        }


        //输入数据
        foreach ($data as $key => &$value) {
            $js = [];
            for ($i = 0; $i < count($key_arr); $i++) {
                $js = array_merge($js, [$key_arr[$i] => $value[$key_arr[$i]] ?? '']);
            }
            array_push($row, $js);
            unset($value);
        }

        // 需要合计
        if ($isHeji === true) {
            $sum_data = [];

            $isset_heji = 0;
            foreach ($key_arr as $vkey) {
                $sum_item = array_column($data, $vkey);
                $numeric_array = array_filter($sum_item, function ($value) use ($vkey) {
                    return is_numeric($value) && (!preg_match('/^1[3456789]\d{9}$/', $value)) && strlen($value) != 12 && (strpos($this->row[count($this->row) - 1][$vkey], '摘要') === false && strpos($this->row[count($this->row) - 1][$vkey], '号') === false && strpos($this->row[count($this->row) - 1][$vkey], '码') === false && strpos($this->row[count($this->row) - 1][$vkey], '费率') === false&& strpos($this->row[count($this->row) - 1][$vkey], '品名') === false&&strpos($this->row[count($this->row) - 1][$vkey], '含量') === false);
                });

                $sum_data[$vkey] = array_sum($numeric_array);
                if ($isset_heji == 0) {
                    $sum_data[$vkey] = '合计';
                    $isset_heji = 1;
                }

            }
            array_push($row, $sum_data);
        }


        return collect($row);
    }


    public function styles(Worksheet $sheet)
    {

        if (!empty($this->mergeCell)) {
            foreach ($this->mergeCell as $v) {
                $sheet->mergeCells($v);
            }
        }
        if (count($this->row) == 3) {
            $sheet->mergeCells('A1:' . $sheet->getHighestDatacolumn() . '1');
            $sheet->mergeCells('A2:' . $sheet->getHighestDatacolumn() . '2');
        }

        if (count($this->row) == 2) {
            $sheet->mergeCells('A1:' . $sheet->getHighestDatacolumn() . '1');
        }


        foreach ($this->generateColumnRange('A', $sheet->getHighestDatacolumn()) as $columnID) {
            $sheet->getColumnDimension($columnID)->setAutosize(true);
        }


        if (count($this->datawith)) {
            foreach ($this->datawith as $key => $val) {
                $sheet->getColumnDimension($key)->setWidth($val);
            }
        }


        $row = $this->row;
        $rowCount =count($row);


        $titlecount = $this->titlecount();

        //  设置单元格文本格式
        $sheet->getStyle('A:' . $titlecount)
            ->getNumberFormat()
          // ->setFormatCode(\PhpOffice\PhpSpreadsheet\Style\NumberFormat::FORMAT_TEXT);
            ->setFormatCode(\PhpOffice\PhpSpreadsheet\Style\NumberFormat::FORMAT_GENERAL);


        //设置不适用科学计数法
        //$sheet->getStyle('A:'.$sheet->getHighestDatacolumn())->getNumberFormat()->setFormatCode('#');

        $styleArray = [
            'borders' => [
                'allBorders' => [
                    'borderStyle' =>StyleBorder::BORDER_THIN, // 边框样式，例如：BORDER_THICK, BORDER_THIN, BORDER_DASHED等
                    'color' => ['argb' => 'FF000000'], // 边框颜色，例如：黑色（黑色ARGB代码为FF000000）
                ],
            ],
        ];


        foreach ($row as $k => $value) {

            $hang = $k + 1;
            // 表头设置
            $sheet->getStyle('A' . $hang . ':' . $titlecount . $hang)->applyFromArray(['alignment' => ['horizontal' => 'center']]);
            $sheet->getStyle('A' . $hang . ':' . $titlecount . $hang)->getFont()->setSize(14);
            $sheet->getStyle('A' . $hang . ':' . $titlecount . $hang)->applyFromArray($styleArray);

            // $sheet->getStyle('A1:C1')->getFont()->getColor()
            //     ->setRGB('FF0000');

            $sheet->getRowDimension($hang)->setRowHeight(25);
            $sheet->getStyle('A' . $hang . ':' . $titlecount . $hang)->getAlignment()->setVertical('center');


            $col = 0;
            foreach ($value as $val) {

                $col += 1;
                if (!empty(strstr($val, '摘要')) || !empty(strstr($val, '号')) || !empty(strstr($val, '码'))) {
                    //设置不适用科学计数法
                    $lie = $this->convertToExcelColumn($col);

                    if (!empty($lie)) {
                        $sheet->getStyle($lie)->getNumberFormat()->setFormatCode(\PhpOffice\PhpSpreadsheet\Style\NumberFormat::FORMAT_TEXT);
                        $sheet->getStyle($lie)->getNumberFormat()->setFormatCode('#');
                    }

                }

            }

        }


        $num =count($this->data) + count($this->row);
        if($this->isHeji===true){
            $num += 1;
        }
        // 数据设置
        $sheet->getStyle("A2:" . $titlecount . $num)->getAlignment()->setHorizontal('center');
        $sheet->getStyle("A2:" . $titlecount . $num)->getAlignment()->setVertical('center');
        $sheet->getStyle("A2:" . $titlecount . $num)->applyFromArray($styleArray);
        if ($rowCount==2){
            $sheet->getStyle('A2:' . $titlecount . '2')->getFill()
                ->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID)
                ->getStartColor()->setRGB('C5D9F1');
        }elseif ($rowCount==3){
            $sheet->getStyle('A2:' . $titlecount . '2')->getAlignment()->setHorizontal('left');
            $sheet->getStyle('A3:' . $titlecount . '3')->getFill()
                ->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID)
                ->getStartColor()->setRGB('C5D9F1');
        }else{
            $sheet->getStyle('A1:' . $titlecount . '1')->getFill()
                ->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID)
                ->getStartColor()->setRGB('C5D9F1');
        }

        $sheet->getStyle('A1:' . $titlecount . '1')->applyFromArray(['alignment' => ['horizontal' => 'center']]);

        if($this->isHeji===true){
            $sheet->getStyle("A$num:" . $titlecount.$num)
                ->getFill()->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID)
                ->getStartColor()->setRGB('8DB4E2');
        }

    }

    protected function titlecount()
    {
        $number = count($this->row[count($this->row) - 1]);
        if (ceil($number / 26) == 1) {
            $remainder = $number % 26; // 1-26
            $excelColumn = $remainder > 0 ? chr($remainder + 64) : 'Z';
        } elseif (ceil($number / 26) == 2) {
            $remainder = $number % 26; // 1-26
            $excelColumn = $remainder > 0 ? 'A' . chr($remainder + 64) : 'AZ';
        } elseif (ceil($number / 26) == 3) {
            $remainder = $number % 26; // 1-26
            $excelColumn = $remainder > 0 ? 'B' . chr($remainder + 64) : 'BZ';
        }

        return $excelColumn;

    }

    public function generateColumnRange($start, $end): array
    {
        $columns = [];

        // 单字母列标识符
        for ($i = ord($start); $i <= ord('Z'); $i++) {
            $columns[] = chr($i);
        }

        // 双字母列标识符
        if (strlen($end) == 2) {
            $firstLetter = substr($end, 0, 1);
            $secondLetter = substr($end, 1, 1);

            for ($i = ord('A'); $i <= ord($firstLetter); $i++) {
                for ($j = ord('A'); $j <= ord($secondLetter); $j++) {
                    $columns[] = chr($i) . chr($j);
                }
            }
        }

        // 返回从 'A' 到 'AZ' 的列标识符数组
        return array_slice($columns, 0, array_search($end, $columns) + 1);
    }


    /**
     * 根据数字换算字母
     * @param $number
     * @return string
     */
    public function convertToExcelColumn($number)
    {
        $number--;
        if ($number < 0) {
            return '';
        }
        return $this->convertToExcelColumn($number / 26) . chr(65 + $number % 26);
    }

}
